#coding:utf-8
"""
# 信息分析和展示
### 5淘宝评论
已知tb_comments_1.json和tb_comments_2.json是从淘宝已获取的小米手机评论中的两页，共40条评论。
用json.load()方法读取json文件信息，
```python
with open('tb_comments_1.json', encoding='utf-8') as f:
    comments = json.load(f)
```
要求:
1. 控制台打印红框标注的所需字段的值（10）
![](3.png)
2. 新建数据库taobao，新建表comments（id, comment_id, rate_content, rate_date, append_content, action_sku）。将第1步的值存入数据库,插入每条评论前根据comment_id验证是否重复插入。（40）
3. 读数据库根据rate_date倒序前30条评论的 评论rate_content和追加评论append_content 字段。（10）
4. 将所有评论拼成一个长字符串，用jieba包进行分词。（10）
5. 根据上一步的评论内容分词结果用wordcloud包生成词云图。（10）
6. 根据action_sku分组统计购买每种配置的用户数，任意图表库输出购买比例饼状图。（30）

"""
import json, pymysql.cursors
import jieba, pygal
from wordcloud import WordCloud

def get_cursor():
    connection = pymysql.connect(
        host='127.0.0.1',
        port=3306,
        user='root',
        password='123456',
        db='menagerie',
        charset='utf8mb4',
    )

    cursor = connection.cursor()
    return cursor

def insert(result):
    connection = pymysql.connect(
        host='127.0.0.1',
        port=3306,
        user='root',
        password='123456',
        db='menagerie',
        charset='utf8mb4',
    )

    cursor = connection.cursor()
    for i in result:
        sql2 = """
             insert into taobao values (%s, %s, %s, %s, %s, %s)"""
        affected = cursor.execute(sql2, (None, i['id'], i['rate_content'], i['rate_date'], i['append_content'], i['action_sku']))
    connection.commit()
    cursor.close()

def desc():
    cursor = get_cursor()
    sql5 = """ SELECT rate_content, append_content FROM taobao order by rate_date desc  """
    cursor.execute(sql5)
    ret = cursor.fetchall()
    return ret

def get_comments():
    # 从数据库把所有用户评论查出
    cursor = get_cursor()
    sql3 = f"select rate_content from taobao"
    cursor.execute(sql3)
    re = cursor.fetchall()
    return re

def process_comments(comments):
    # 所有用户评论拼成一个长字符串
    str = ''
    for i in comments:
        str = str + i[0]
    return str

def cut_word(string):
    # 分词 返回wordcloud包使用的格式
    results = jieba.cut(string, cut_all=False)
    str1 = ""
    for r in results:
        # print(r)
        str1 = str1 + r + " "
    return str1

def word_cloud(string):
    font = 'msyh.ttf'
    wc = WordCloud(font_path=font,
                   background_color='white',
                   width=1000,
                   height=800,
                   max_font_size=300,
                   min_font_size=1,
                   ).generate(string)

    wc.to_file('淘宝评论.png')


def gen_pei(comments=None):
    # 生成饼状图
    assert comments is not None,'chart_by_color方法comments参数不能为空'
    print('开始生成图表')

    sql3 = """ select count(id) as amount, action_sku from taobao group by action_sku;"""
    cursor = get_cursor()
    cursor.execute(sql3)
    re_set = cursor.fetchall()
    if not re_set:
        print('sql3:{}  查询结果集空，无法生成饼状图'.format(sql3))

    # 生成图表
    pie_chart = pygal.Pie()
    pie_chart.title = '手机配置占比图（由用户评论估算）'

    amount = 0  # 购买总人数，用于计算比例
    for rs in re_set:
        amount += rs[0]

    for rs in re_set:
        pie_chart.add(rs[1], round(rs[0] / amount * 100, 1))
    pie_chart.render_to_file('pie.svg')

    print('已生成图表，路径{}'.format('pie.svg'))
    return None



if __name__ == '__main__':
    with open('tb_comments_1.json', encoding='utf-8') as f:
        comments = json.load(f)
        result = []
        comment2 = comments['rateDetail']['rateList']
        # print(comment2)
        for comment in comment2:
            cont = comment['appendComment']
            if cont is None:
                content = '暂无评论'
            else:
                content = cont['content']
            id = comment['id']
            sql0 = """select comment_id from taobao where comment_id={}""".format(id)
            cursor = get_cursor()
            cursor.execute(sql0)
            re = cursor.fetchall()
            if re:
                print('数据已存在')
                continue
            # print(re)
            rateDate = comment['rateDate']
            rateContent = comment['rateContent']
            auctionSku = comment['auctionSku']
            result.append({
                'id': id,
                'rate_content': rateContent,
                'rate_date': rateDate,
                'append_content': content,
                'action_sku': auctionSku})
        # print(result)

        insert2 = insert(result)
        desc = desc()
        print(desc)
        # comments = get_comments()
        # string = process_comments(comments)
        # word_fenci = cut_word(string)
        # ciyun = word_cloud(string=word_fenci)
        # gen_pei(comments)
        # print('Done')

